import openpyxl
import pymysql
import yaml

from openpyxl import load_workbook

host='localhost'
port=3306
user='root'
password='root'
database = 'finance'
charset='utf8'
class read_data:
    # 读取excel表格
    @staticmethod
    def read_data_excel(filename, sheet):
        try:
            wb = load_workbook(filename=filename)
            st = wb[sheet]
            data = [[st.cell(row=i, column=j).value for j in range(1, st.max_column + 1)] for i in
                    range(2, st.max_row + 1)]
            return data
        except Exception as e:
            print('文件操作有问题', e)
        finally:
            wb.save(filename)
    #读取yaml
    @staticmethod
    def readyaml(filepath):
        file = open(file=filepath, mode='rb')
        data = yaml.load(file, Loader=yaml.SafeLoader)
        return data
    #读取mysql
    @staticmethod
    def databases_operations(sql, param=None):
        db = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            charset=charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, param)
                db.commit()
                print('操作成功')
        except Exception as e:
            print('操作有问题', e)
        finally:
            cursor.close()
            db.close()

    # 查找操作
    def select(sql, param=None):
        db = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database,
            charset=charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql, param)
                db.commit()
                print('操作成功')
                data = cursor.fetchall()
                return (data)
                # for i in data:
                #     print(i)
        except Exception as e:
            print('操作有问题', e)
        finally:
            cursor.close()
            db.close()